package itheima01;

import domain.Users;
import utils.JDBCUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

/*
    使用JDBCUtil工具类查询封装成对象和集合

    JDBC的开发步骤
        1.使用JDBCUtil工具类获取连接Connection对象
        2.Connection对象获取执行sql语句的Statement对象
        3.Statement对象执行增删改查,获取结果
        4.封装结果到JavaBean
        5.关闭资源

 */
public class Demo02Query {
    public static void main(String[] args) throws Exception {
        //调用方法: 根据uid查询
        String uid = "u001";
        Users user = getUsersByUid(uid);
        if(user!=null) {
            System.out.println("查询结果: "+user);
        } else {
            System.out.println("没有查询到信息~~~");
        }
        System.out.println("------------");
        //调用方法,查询所有
        List<Users> list = queryAllUsers();
        if(list.size()!=0) {
            for (Users u : list) {
                System.out.println(u);
            }
        } else {
            System.out.println("没有查询到信息~~~");
        }
    }
    //定义方法,查询所有信息,返回List<Users>集合对象
    public static List<Users> queryAllUsers() throws Exception {

        //创建List集合对象,存储查询到的多个Users对象
        List<Users> list = new ArrayList<>();

        //1.使用JDBCUtil工具类获取连接Connection对象
        Connection con = JDBCUtil.getConnection();

        //2.Connection对象获取执行sql语句的Statement对象
        Statement stmt = con.createStatement();

        //定义sql语句
        String sql = "select * from users";
        //System.out.println(sql);
        //3.Statement对象执行增删改查,获取结果
        ResultSet rs = stmt.executeQuery(sql);

        //4.封装结果到JavaBean
        while(rs.next()) {
            String uid = rs.getString("uid");
            String uname = rs.getString("uname");
            String upass = rs.getString("upass");

            //创建对象
            Users user = new Users(uid,uname,upass);
            //添加到集合
            list.add(user);
        }

        //5.关闭资源
        JDBCUtil.release(con,stmt,rs);
        //返回结果
        return list;


    }
    //定义方法,根据uid的值查询数据库,返回一个Users对象
    public static Users getUsersByUid(String uid) throws Exception {
        //1.使用JDBCUtil工具类获取连接Connection对象
        Connection con = JDBCUtil.getConnection();

        //2.Connection对象获取执行sql语句的Statement对象
        Statement stmt = con.createStatement();

        //定义sql语句
        String sql = "select * from users where uid='"+uid+"'";
        System.out.println(sql);
        //3.Statement对象执行增删改查,获取结果
        ResultSet rs = stmt.executeQuery(sql);
        //创建Users对象
        Users user = null;
        //4.封装结果到JavaBean
        if(rs.next()) {
            uid = rs.getString("uid");
            String uname = rs.getString("uname");
            String upass = rs.getString("upass");
            //创建对象
            user = new Users(uid,uname,upass);
        }

        //5.关闭资源
        JDBCUtil.release(con,stmt,rs);
        //返回结果
        return user;


    }

}

/*
public class Demo02Query {
    public static void main(String[] args) throws SQLException {
        Scanner scanner=new Scanner(System.in);
        System.out.println("请输入查询的主键:");
        String uid=scanner.nextLine();

        Users user=getUsersByUid(uid);
        if(user!=null) {
            System.out.println("查询结果: "+user);
        } else {
            System.out.println("没有查询到信息~~~");
        }
        System.out.println("------------");

        List<Users>list=queryAllUsers();
        if(list.size()!=0){
            for (Users users : list) {
                System.out.println(users);
            }
        }else{
            System.out.println("未查询出数据");
        }

    }
    //定义方法,查询所有信息,返回List<Users>集合对象
    public  static List<Users> queryAllUsers() throws SQLException {
        //创建List集合对象,存储查询到的多个Users对象
        List<Users>list=new ArrayList<>();
        //1.使用JDBCUtil工具类获取连接Connection对象
        Connection connection=JDBCUtil.getConnection();
        //2.Connection对象获取执行sql语句的Statement对象
        Statement statement=connection.createStatement();
        //定义sql语句
        String sql="select * from users";
        System.out.println(sql);
        ResultSet resultSet = statement.executeQuery(sql);
        //3.Statement对象执行增删改查,获取结果
        Users users=null;
        while (resultSet.next()){
        String  uid=resultSet.getString("uid");
        String  uname = resultSet.getString("uname");
        String  upass = resultSet.getString("upass");
        users=new Users(uid,uname,upass);
        //添加到集合里面去
            list.add(users);
        }
        //4.封装结果到JavaBean
        //关闭资源
        JDBCUtil.release(connection,statement,resultSet);
        return  list;
    }
    public static Users getUsersByUid(String uid) throws SQLException {
        //1.使用JDBCUtil工具类获取连接Connection对象
        Connection connection = JDBCUtil.getConnection();
        //2.Connection对象获取执行sql语句的Statement对象
        Statement statement = connection.createStatement();
        //3.Statement对象执行增删改查,获取结果
        String sql = "select * from users where uid='"+uid+"'";
        System.out.println(sql);
        ResultSet resultSet = statement.executeQuery(sql);
        //创建Users对象
        Users user = null;
        if (resultSet.next()){
            uid=resultSet.getString("uid");
            String upass=resultSet.getString("upass");
            String uname=resultSet.getString("uname");
            user=new Users(uid,upass,upass);
        }
        //4.封装结果到JavaBean
        //5.关闭资源
        JDBCUtil.release(connection,statement,resultSet);
        return  user;
    }
}*/
